package com.F04.dao;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.log4j.Logger;

import com.F04.actionForm.F0402ActionForm;
import com.dao.common.DbOperationExt;
import com.util.CommonTools;

public class F0402Dao extends DbOperationExt {

	Logger log = Logger.getLogger(F0401Dao.class);

	public F0402Dao() {

	}

	public F0402Dao(Connection conn) {
		super(conn);
	}

	public List select(F0402ActionForm actionForm) throws SQLException {

		List list = new ArrayList();
		StringBuffer sql = new StringBuffer();
		Map map = null;
		ResultSet rest = null;

		String materialName = CommonTools.null2String(actionForm
				.getMaterialName());
		String materialCode1 = CommonTools.null2String(actionForm
				.getMaterialCode1());
		String materialCode2 = CommonTools.null2String(actionForm
				.getMaterialCode2());

		sql.append("select m.code,m.name,quantity,u.name measuring_unit from pc_material_quantity c,t_materials m,t_measure_units u");
		sql.append(" where m.code = c.material_code and m.measure_code = u.id ");
		if (materialCode1 != "") {
			sql.append(" and m.code >= '" + materialCode1 + "'");
		}
		if (materialCode2 != "") {
			sql.append(" and m.code <= '" + materialCode2 + "'");
		}
		if (materialName != "") {
			sql.append(" and m.name like '%" + materialName + "%'");
		}

		log.info(sql.toString());
		rest = super.executeQuery(sql.toString());
		while (rest.next()) {
			map = new HashMap();
			map.put("materialCode", CommonTools.null2String(rest
					.getString("code")));
			map.put("materialName", CommonTools.null2String(rest
					.getString("name")));
			map.put("measuringUnit", CommonTools.null2String(rest.getString("measuring_unit")));
			map.put("qty", String.valueOf(rest.getInt("quantity")));
			list.add(map);
		}
		if (map == null) {
			actionForm.setErrorFlag("2");
			return null;
		}
		return list;
	}

	public boolean upload(F0402ActionForm actionForm){

		boolean flag = false;
		String strUrl = actionForm.getStrUrl();
		StringBuffer sql = null;
		StringBuffer sqlDel = new StringBuffer();
		int i = 0;
		Workbook book = null;
		try {
			
//			删除原有的数据
			sqlDel.append("delete from pc_material_quantity");
			super.executeUpdate(sqlDel.toString(),false);
			
			book = Workbook.getWorkbook(new File(strUrl));
			Sheet sheet = book.getSheet(0);
			log.info(sheet.getRows()+"");
			for (i=0;i < sheet.getRows(); i++) {
				sql = new StringBuffer();
				sql.append("insert into pc_material_quantity(material_code,quantity) values (");
				sql.append("'" + sheet.getCell(0, i).getContents() + "',"
						+ sheet.getCell(1, i).getContents() + ")");
				log.info(sheet.getCell(1, i).getContents());
				super.executeUpdate(sql.toString(),false);	
			}
			flag = true;
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			actionForm.setErrorFlag("1");
			return false;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			actionForm.setErrorFlag("1");
			return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			actionForm.setUploadError((i+1)+"");
			actionForm.setErrorFlag("1");
			return false;
		}finally{
			book.close();
		}
		return flag;
	}
	
	public void selectByCode(F0402ActionForm actionForm) throws SQLException{
		
		StringBuffer sql = new StringBuffer();
		ResultSet rest = null;
		
		sql.append("select m.code,m.name,quantity,u.name measuring_unit from pc_material_quantity c,t_materials m,t_measure_units u ");
		sql.append("where m.code = c.material_code and m.measure_code = u.id and ");
		sql.append("	m.code = '"+actionForm.getMaterialCode()+"'");
		
		log.info(sql.toString());
		rest = super.executeQuery(sql.toString());
		while(rest.next()){
			actionForm.setMaterialCode(CommonTools.null2String(rest.getString("code")));
			actionForm.setMaterialName(CommonTools.null2String(rest.getString("name")));
			actionForm.setMaterialQty(String.valueOf(rest.getInt("quantity")));
			actionForm.setMeasuringUnit(CommonTools.null2String(rest.getString("measuring_unit")));
		}	
	}
	
	public void update(F0402ActionForm actionForm) throws SQLException{
		
		StringBuffer sql = new StringBuffer();
		sql.append("update pc_material_quantity set quantity = "+actionForm.getMaterialQty());
		sql.append(" where material_code = '"+CommonTools.null2String(actionForm.getMaterialCode())+"'");
		
		log.info(sql.toString());
		super.executeUpdate(sql.toString());
	}
	
	public void delete(F0402ActionForm actionForm) throws SQLException{
		
		StringBuffer sql = new StringBuffer();
		sql.append("delete from pc_material_quantity ");
		sql.append(" where material_code = '"+CommonTools.null2String(actionForm.getId())+"'");
		log.info(sql.toString());
		
		super.executeUpdate(sql.toString());
	}
}
